SnowflakeのQuickstart「Build a Snowflake Native App with Snowpark Container Services」をやってみた

git clone https://github.com/Snowflake-Labs/sfguide-build-a-native-app-with-spcs

use role accountadmin;
create role if not exists naspcs_role;
grant role naspcs_role to role accountadmin;
grant create integration on account to role naspcs_role;
grant create compute pool on account to role naspcs_role;
grant create warehouse on account to role naspcs_role;
grant create database on account to role naspcs_role;
grant create application package on account to role naspcs_role;
grant create application on account to role naspcs_role with grant option;
grant bind service endpoint on account to role naspcs_role;

use role naspcs_role;
create database if not exists spcs_app;
create schema if not exists spcs_app.napp;
create stage if not exists spcs_app.napp.app_stage;
create image repository if not exists spcs_app.napp.img_repo;
create warehouse if not exists wh_nap with warehouse_size='xsmall';

use role accountadmin;
create role if not exists nac;
grant role nac to role accountadmin;
create warehouse if not exists wh_nac with warehouse_size='xsmall';
grant usage on warehouse wh_nac to role nac with grant option;
grant imported privileges on database snowflake_sample_data to role nac;
grant create database on account to role nac;
grant bind service endpoint on account to role nac with grant option;
grant create compute pool on account to role nac;
grant create application on account to role nac;

use role nac;
create database if not exists nac_test;
create schema if not exists nac_test.data;
use schema nac_test.data;
create view if not exists orders as select * from snowflake_sample_data.tpch_sf10.orders;

use role naspcs_role;
show image repositories in schema spcs_app.napp;

docker login <SNOWFLAKE_REPO>

cd backend
docker build --platform linux/amd64 -t eap_backend .
cd ..
docker tag eap_backend <SNOWFLAKE_REPO>/eap_backend
docker push <SNOWFLAKE_REPO>/eap_backend

cd frontend  
docker build --platform linux/amd64 -t eap_frontend . 
cd ..
docker tag eap_frontend <SNOWFLAKE_REPO>/eap_frontend
docker push <SNOWFLAKE_REPO>/eap_frontend

cd router 
docker build --platform linux/amd64 -t eap_router . 
cd ..
docker tag eap_router <SNOWFLAKE_REPO>/eap_router
docker push <SNOWFLAKE_REPO>/eap_router

show images in image repository spcs_app.napp.img_repo;

CREATE APPLICATION ROLE app_admin;
CREATE APPLICATION ROLE app_user;
CREATE SCHEMA IF NOT EXISTS app_public;
GRANT USAGE ON SCHEMA app_public TO APPLICATION ROLE app_admin;
GRANT USAGE ON SCHEMA app_public TO APPLICATION ROLE app_user;
CREATE OR ALTER VERSIONED SCHEMA v1;
GRANT USAGE ON SCHEMA v1 TO APPLICATION ROLE app_admin;

CREATE PROCEDURE v1.register_single_callback(ref_name STRING, operation STRING, ref_or_alias STRING)
 RETURNS STRING
 LANGUAGE SQL
 AS $$
      BEGIN
      CASE (operation)
         WHEN 'ADD' THEN
            SELECT system$set_reference(:ref_name, :ref_or_alias);
         WHEN 'REMOVE' THEN
            SELECT system$remove_reference(:ref_name);
         WHEN 'CLEAR' THEN
            SELECT system$remove_reference(:ref_name);
         ELSE
            RETURN 'Unknown operation: ' || operation;
      END CASE;
      RETURN 'Operation ' || operation || ' succeeds.';
      END;
   $$;
GRANT USAGE ON PROCEDURE v1.register_single_callback( STRING,  STRING,  STRING) TO APPLICATION ROLE app_admin;

CREATE OR REPLACE PROCEDURE app_public.start_app(poolname VARCHAR, whname VARCHAR)
    RETURNS string
    LANGUAGE sql
    AS $$
BEGIN
        EXECUTE IMMEDIATE 'CREATE SERVICE IF NOT EXISTS app_public.st_spcs
            IN COMPUTE POOL Identifier(''' || poolname || ''')
            FROM SPECIFICATION_FILE=''' || '/fullstack.yaml' || '''
            QUERY_WAREHOUSE=''' || whname || '''';
GRANT USAGE ON SERVICE app_public.st_spcs TO APPLICATION ROLE app_user;
GRANT SERVICE ROLE app_public.st_spcs!ALL_ENDPOINTS_USAGE TO APPLICATION ROLE app_user;

RETURN 'Service started. Check status, and when ready, get URL';
END;
$$;
GRANT USAGE ON PROCEDURE app_public.start_app(VARCHAR, VARCHAR) TO APPLICATION ROLE app_admin;

CREATE OR REPLACE PROCEDURE app_public.stop_app()
    RETURNS string
    LANGUAGE sql
    AS
$$
BEGIN
    DROP SERVICE IF EXISTS app_public.st_spcs;
END
$$;
GRANT USAGE ON PROCEDURE app_public.stop_app() TO APPLICATION ROLE app_admin;

CREATE OR REPLACE PROCEDURE app_public.app_url()
    RETURNS string
    LANGUAGE sql
    AS
$$
DECLARE
    ingress_url VARCHAR;
BEGIN
    SHOW ENDPOINTS IN SERVICE app_public.st_spcs;
    SELECT "ingress_url" INTO :ingress_url FROM TABLE (RESULT_SCAN (LAST_QUERY_ID())) LIMIT 1;
    RETURN ingress_url;
END
$$;
GRANT USAGE ON PROCEDURE app_public.app_url() TO APPLICATION ROLE app_admin;
GRANT USAGE ON PROCEDURE app_public.app_url() TO APPLICATION ROLE app_user;

spec:
  containers:
    - name: eap-frontend
      image: /spcs_app/napp/img_repo/eap_frontend
      env:
        VUE_APP_API_URL: /api/snowpark
    - name: eap-backend
      image: /spcs_app/napp/img_repo/eap_backend
    - name: eap-router
      image: /spcs_app/napp/img_repo/eap_router
      env:
        FE_SERVER: 127.0.0.1:8080
        BE_SERVER: 127.0.0.1:8081
  endpoints:
    - name: app
      port: 8000
      public: true

#version identifier
manifest_version: 1

version:
  name: V1
  label: "Version One"
  comment: "The first version of our Native Application that includes SPCS"

#artifacts that are distributed from this version of the package
artifacts:
  readme: readme.md
  setup_script: setup.sql
  container_services:
    images:
      - /spcs_app/napp/img_repo/eap_frontend
      - /spcs_app/napp/img_repo/eap_backend
      - /spcs_app/napp/img_repo/eap_router

privileges:
 - CREATE COMPUTE POOL:
      description: "Enable application to create its own compute pool(s)"
 - BIND SERVICE ENDPOINT:
      description: "Enables application to expose service endpoints"
 - CREATE WAREHOUSE:
      description: "Enables application to create its own warehouse(s)"

references:
  - ORDERS_TABLE:
      label: "Orders View"
      description: "View created on Orders table from TPC-H samples"
      privileges:
        - SELECT
      object_type: VIEW
      multi_valued: false
      register_callback: v1.register_single_callback

# build-a-native-app-with-spcs
 How to Build a Native App that includes Snowpark Container Services

use role naspcs_role;
create application package spcs_app_pkg;
alter application package spcs_app_pkg add version v1 using @spcs_app.napp.app_stage;
grant install, develop on application package spcs_app_pkg to role nac;

show versions in application package spcs_app_pkg;

use role nac;
create application spcs_app_instance from application package spcs_app_pkg using version v1;

use database nac_test;
use role nac;
create  compute pool pool_nac for application spcs_app_instance
    min_nodes = 1 max_nodes = 1
    instance_family = cpu_x64_s
    auto_resume = true;

grant usage on compute pool pool_nac to application spcs_app_instance;
grant usage on warehouse wh_nac to application spcs_app_instance;
grant bind service endpoint on account to application spcs_app_instance;
CALL spcs_app_instance.v1.register_single_callback(
  'ORDERS_TABLE' , 'ADD', SYSTEM$REFERENCE('VIEW', 'NAC_TEST.DATA.ORDERS', 'PERSISTENT', 'SELECT'));

call spcs_app_instance.app_public.start_app('POOL_NAC', 'WH_NAC');

call spcs_app_instance.app_public.app_url();

--clean up consumer objects
use role nac;
drop application spcs_app_instance cascade;
drop compute pool pool_nac;
drop database nac_test;

--clean up provider objects
use role naspcs_role;
drop application package spcs_app_pkg;
drop database spcs_app;
drop warehouse wh_nap;

--clean up prep objects
use role accountadmin;
drop warehouse wh_nac;
drop role naspcs_role;
drop role nac;